package org.yoao.crm.tools;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import jxl.*;
import jxl.format.UnderlineStyle;
import jxl.write.DateFormat;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class ExcelTool {
	private static final Logger log = Logger.getLogger(ExcelTool.class);
	public String info;
	private File file;
	private final String sheetName = "YOAO客户信息";

	public void setFilePath(String filePath){
		this.file = new File(filePath);
	}

	public List read(String[] title){
		Workbook book = null;
		List result = new ArrayList();
		try{
			book = Workbook.getWorkbook(file);
			Sheet[] sheets = book.getSheets();
			if(sheets != null && sheets.length>0){
				for(int s=0;s<sheets.length;s++){
					Sheet sheet = sheets[s];
					String name = sheet.getName();
					if(!name.equals("YOAO客户信息")){
						log.info("不可识别的工作薄:"+name);
						info = "</BR>excel文件中含不可识别的工作薄:"+name+"工作薄名称只能是:'YOAO客户信息'.";
						continue;
					}

					int columns = sheet.getColumns();
					int rows = sheet.getRows();

					for(int i=1;i<rows;i++){
						Map map = new HashMap();
						for(int k=0;k<columns;k++){
							Cell cell = sheet.getCell(k,i);//(列,行)
							map.put(title[k], cell.getContents());
						}
						result.add(map);
					}
				}
			}
			return result;
		}
		catch(Exception e){
			info = "</BR>" + e.toString();
			e.printStackTrace();
			return result;
		}
		finally{
			if(book != null) book.close();
			log.debug("EXCEL read over!");
		}
	}

	public void write(List data, Map label, String[] title, int dateIndex){
		WritableWorkbook book = null;
		try{
			log.debug("开始写入EXCEL...");
			book = Workbook.createWorkbook(file);
			WritableSheet sheet = book.createSheet(sheetName, 0);

			WritableFont font = new WritableFont(WritableFont.createFont("楷体_GB2312"),
													12,
													WritableFont.BOLD,
													false,
													UnderlineStyle.NO_UNDERLINE,
													jxl.format.Colour.RED);
			WritableCellFormat format = new WritableCellFormat(font);
			format.setAlignment(jxl.format.Alignment.CENTRE);
			format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
			format.setLocked(true);


			if(title != null){
				for(int i=0;i<title.length;i++){
					sheet.addCell(new Label(i, 0, label.get(title[i]).toString(), format));
					sheet.setColumnView(i, 25);
				}
			}

			if(data != null && !data.isEmpty()){
				WritableFont detFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
				DateFormat df = new DateFormat("yyyy-MM-dd");
				int size = data.size();
				for(int i=0;i<size;i++){
					Map customer = (Map)data.get(i);
					for(int j=0;j<title.length;j++){
						String value = (String)customer.get(title[j]);
						if(value == null || value.equals("null")) value = "";
						if(j == dateIndex){
							WritableCellFormat dateFormat = new WritableCellFormat (detFont, df);
							sheet.addCell(new Label(j, i+1, value, dateFormat));
						}else
							sheet.addCell(new Label(j, i+1, value));
					}
				}
			}
			else log.debug("数据为空,未写入数据到excel中.");
			book.write();
		}
		catch(Exception e){
			info = "</BR>" + e.toString();
			e.printStackTrace();
		}
		finally{
			try {
				if(book != null) book.close();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
			log.debug("EXCEL写入完毕!");
		}
	}
}